<?php
/**
 * Created by muniao.
 * User: muniao
 * Date: 2018/7/31
 * Time: 16:00
 */

namespace app\api\controller\android\v12;


use app\api\model\Envelopes;
use think\Cache;
use think\Controller;
use app\api\model\Authority;
use think\db;

use think\Request;
use  app\api\model\OrderModel;

class Financial extends Controller
{
    protected $model;
    protected $us;

    public function _initialize()
    {
        $this->model = new Authority();
        $this->us    = $this->model->check(1);
    }

    public function state()
    {
        $data = [
            [
                'type' => 1,
                'title' => '签约时间',
            ],
            [
                'type' => 2,
                'title' => '完工时间',
            ],
            [
                'type' => 3,
                'title' => '完款时间',
            ],
            [
                'type' => 4,
                'title' => '结算时间',
            ],
        ];
        r_date($data, 200);
    }

    public function user()
    {
        $user = \db('user')->where('store_id', $this->us['store_id'])->where('status', 0)->field('user_id,username')->select();
        r_date($user, 200);
    }

    /*
     * 全部店长
     */
    public function whole()
    {
        $data  = \request()->get();
        $users = \db('user');
        if (isset($data['user_name']) && $data['user_name'] != '') {
            $users->whereLike('user.username', "%{$data['user_name']}%");
        }
        if (isset($data['store_id']) && $data['store_id'] != 0) {
            $users->whereLike('user.store_id', $data['store_id']);
        }
        $user = $users->where('user.status', 0)->join('store', 'store.store_id=user.store_id', 'left')->field('user.user_id,user.username,user.mobile,user.work_tag,store.store_name,user.store_id')->select();
        foreach ($user as $k => $v) {
            $work_tag = empty($v['work_tag']) ? [0, 0, 0, 0] : explode(',', $v['work_tag']);

            $user[$k]['work_tags'] = [];
            foreach ($work_tag as $ks => $value) {
                switch ($ks) {
                    case 0:
                        $values = '建筑渗漏水维修专家';
                        break;
                    case 1:
                        $values = '旧房改造类专家';
                        break;
                    case 2:
                        $values = '墙面维修专家';
                        break;
                    case 3:
                        $values = '室内渗漏水维修专家';
                        break;
                }

                if ($value == 1) {
                    $user[$k]['work_tags'][] = $values;
                }
            }
        }
        r_date($user, 200);
    }

    /*
     * 店铺成交量
     * 签约时间，完工时间，完款时间，结算时间
     */
    public function ShopClose(OrderModel $orderModel)
    {
        $params = \request()->get();
        $start  = strtotime(date('Y-m-01 00:00:00', strtotime($params['startTime'])));//获取指定月份的第一天
        $end    = strtotime(date('Y-m-t 23:59:59', strtotime($params['startTime']))); //获取指定月份的最后一天

        $m = $orderModel->table('order')
            ->alias('a')
            ->field('concat(p.province,c.city,u.county,a.addres) as  addres,a.contacts,co.con_time,a.order_id,a.state,go.title,a.assignor')
            ->join('province p', 'a.province_id=p.province_id', 'left')
            ->join('city c', 'a.city_id=c.city_id', 'left')
            ->join('county u', 'a.county_id=u.county_id', 'left')
            ->join('user us', 'a.assignor=us.user_id', 'left')
            ->join('goods_category go', 'a.pro_id=go.id', 'left')
            ->join('contract co', 'a.contract_id=co.contract_id', 'left');
        if (isset($params['user_id']) && $params['user_id'] != 0) {
            $m->where('a.assignor', $params['user_id']);
        }
        switch ($params['type']) {
            //签约时间
            case 1:
                $m->whereBetween('co.con_time', [$start, $end]);
                $m->order('co.con_time desc');
                break;
            //完工时间
            case 2:
                $m->whereBetween('a.finish_time', [$start, $end]);
                $m->order('a.finish_time desc');
                break;
            //完款时间
            case 3:
                $m->whereBetween('a.received_time', [$start, $end]);
                $m->order('a.received_time desc');
                break;
            //结算时间
            default :
                $m->whereBetween('a.cleared_time', [$start, $end]);
                $m->order('a.cleared_time desc');
                break;
        }
        $list = $m->where('us.store_id', $this->us['store_id'])->where('us.ce', '<>', 2)->where('a.state', '<>', 9)->select();

        foreach ($list as $k => $key) {
            //签约时间
            $list[$k]['con_time'] = !empty($key['con_time']) ? date('Y-m-d H:i:s', $key['con_time']) : '';
            $mouth                = $orderModel->offer($key['order_id']);
            //签约金额
            $list[$k]['amount'] = sprintf('%.2f', $mouth['amount']);
            //代购主材
            $list[$k]['agency'] = sprintf('%.2f', $mouth['agency']);
        }
        if (isset($params['Test']) && $params['Test'] == 1) {
            res_date($list, 200);
        } else {
            r_date($list, 200);
        }
    }

    /*
     * 店铺财务
     * 预算/结算
     */
    public function ShopFinance(OrderModel $orderModel)
    {

        $params = \request()->get();
        $start  = strtotime(date('Y-m-01 00:00:00', strtotime($params['startTime'])));//获取指定月份的第一天
        $end    = strtotime(date('Y-m-t 23:59:59', strtotime($params['startTime']))); //获取指定月份的最后一天

        $m = $orderModel->table('order')
            ->alias('a')
            ->field('concat(p.province,c.city,u.county,a.addres) as  addres,a.contacts,co.con_time,a.order_id,a.state,a.assignor,go.title')
            ->join('province p', 'a.province_id=p.province_id', 'left')
            ->join('city c', 'a.city_id=c.city_id', 'left')
            ->join('county u', 'a.county_id=u.county_id', 'left')
            ->join('user us', 'a.assignor=us.user_id', 'left')
            ->join('goods_category go', 'a.pro_id=go.id', 'left')
            ->join('contract co', 'a.contract_id=co.contract_id', 'left');
        if (isset($params['user_id']) && $params['user_id'] != 0) {
            $m->where('a.assignor', $params['user_id']);
        }
        switch ($params['type']) {
            //签约时间
            case 1:
                $m->whereBetween('co.con_time', [$start, $end]);
                $m->order('co.con_time desc');
                break;
            //完工时间
            case 2:
                $m->whereBetween('a.finish_time', [$start, $end]);
                $m->order('a.finish_time desc');
                break;
            //完款时间
            case 3:
                $m->whereBetween('a.received_time', [$start, $end]);
                $m->order('a.received_time desc');
                break;
            //结算时间
            default :
                $m->whereBetween('a.cleared_time', [$start, $end]);
                $m->order('a.cleared_time desc');
                break;
        }
        $list = $m->where('us.store_id', $this->us['store_id'])->where('us.ce', '<>', 2)->where('a.state', '<>', 9)->select();
        foreach ($list as $k => $key) {
            $data                 = [];
            $list[$k]['con_time'] = !empty($key['con_time']) ? date('Y-m-d H:i:s', $key['con_time']) : '';
            $mouth                = $orderModel->offer($key['order_id']);
            //签约金额
            $list[$k]['amount'] = sprintf('%.2f', $mouth['amount']);
            //代购主材
            $list[$k]['agency'] = sprintf('%.2f', $mouth['agency']);
            $da['ification']    = 2;
            $da['order_id']     = $key['order_id'];
            $data[]             = $da;
            //报销金额
            $list[$k]['agency_sum'] = sprintf('%.2f', db('reimbursement')->where(['order_id' => $key['order_id'], 'status' => 1, 'classification' => ['<>', 4]])->sum('money'));
            if (isset($params['expected'])) {
                //人工预算
                $list[$k]['estimate'] = sprintf('%.2f', db('capital')->where(['ordesr_id' => $key['order_id'], 'types' => 1, 'enable' => 1, ' agency' => 0])->sum('labor_cost'));
                //材料预算
                $list[$k]['MaterialBudget'] = sprintf('%.2f', Financial::budget($key['order_id']));
            } else {
                $capital = $cap = db('capital')->where(['ordesr_id' => $key['order_id'], 'types' => 1, 'enable' => 1])->field('class_b,capital_id')->column('capital_id');
                if (!empty($capital)) {
                    $sql = "SELECT SUM(`personal_price`) AS sum FROM app_user_order_capital AS a, (SELECT b.`user_id`, b.`order_id`,b.`capital_id`,MAX(b.created_at) AS `created_at` FROM app_user_order_capital AS b GROUP BY b.`user_id`,b.`order_id`,b.`capital_id`)AS c WHERE a.`user_id`=c.`user_id` AND a.`order_id`=c.`order_id` AND a.`capital_id`=c.`capital_id` AND a.created_at = c.created_at And a.`deleted_at` IS NULL AND a.`capital_id` in(" . implode(',', $capital) . ")";
                    //原生sql
                    $reality_artificial = Db::connect(config('database.db2'))->table('app_user_order_capital');
                    $sum                = $reality_artificial->query($sql);
                    if (isset($sum[0]['sum'])) {
                        $order_for_reality_artificial = round($sum[0]['sum'], 2);
                    } else {
                        $order_for_reality_artificial = 0;
                    }
                } else {
                    $order_for_reality_artificial = 0;
                }
                //人工实际
                $list[$k]['estimate'] = sprintf('%.2f', $order_for_reality_artificial);
                //材料实际
                $list[$k]['MaterialBudget'] = sprintf('%.2f', db('material_usage')
                    ->whereIn('order_id', $key['order_id'])
                    ->where('status', 1)
                    ->sum('total_price'));
            }
            //利润
            $list[$k]['profit'] = sprintf('%.2f', $list[$k]['amount'] - $list[$k]['estimate'] - $list[$k]['MaterialBudget'] - $list[$k]['agency'] - $list[$k]['agency_sum']);

        }
        if (isset($params['Test']) && $params['Test'] == 1) {
            r_date($list, 200);
        } else {
            r_date($list, 200);
        }


    }


    /*
     * 店铺费用查看
     */
    public function shopExpenses($inside = 1, $startTimes = 0)
    {

        $params = \request()->get();
        if (isset($params['startTime'])) {
            $startTime = $params['startTime'];
        } else {
            $startTime = $startTimes;
        }

        $start  = strtotime(date('Y-m-01 00:00:00', strtotime($startTime)));//获取指定月份的第一天
        $end    = strtotime(date('Y-m-t 23:59:59', strtotime($startTime))); //获取指定月份的最后一天
        $subsql = db('store_cost')
            ->where('store_id', $this->us['store_id'])
            ->where('created_time', '<=', $end)
            ->field('store_id,cost_type,max(created_time) as created_time')
            ->group('store_id,cost_type')
            ->buildSql();

        $data = db('store_cost')->alias('a')
            ->join([$subsql => 'b'], 'a.store_id=b.store_id and a.cost_type=b.cost_type and a.created_time=b.created_time')
            ->order('a.store_id,a.cost_type')
            ->field('a.cost_type,a.money')
            ->order('a.cost_type ASC')
            ->select();

        $date = [
            [
                'cost_type' => 14,
                'money' => '0.00',
            ],
            [
                'cost_type' => 15,

                'money' => '0.00',
            ],
            [
                'cost_type' => 16,

                'money' => '0.00',
            ],
            [
                'cost_type' => 17,

                'money' => '0.00',
            ],

        ];
        $d    = array_merge($data, $date);

        foreach ($d as $j => $datum) {
            switch ($datum['cost_type']) {
                case 1:
                    $data[$j]['cost_name'] = '办公费';
                    break;
                case 2:
                    $data[$j]['cost_name'] = '基本工资';
                    break;
                case 3:
                    $data[$j]['cost_name'] = '运输费';
                    break;
                case 4:
                    $data[$j]['cost_name'] = '折旧费';
                    break;
                case 5:
                    $data[$j]['cost_name'] = '房租';
                    break;
                case 6:
                    $data[$j]['cost_name'] = '福利费';
                    break;
                case 7:
                    $data[$j]['cost_name'] = '水电物管费';
                    break;
                case 8:
                    $data[$j]['cost_name'] = '招待费';
                    break;
                case 9:
                    $data[$j]['cost_name'] = '维修费';
                    break;
                case 10:
                    $data[$j]['cost_name'] = '脚手架费用';
                    break;
                case 11:
                    $data[$j]['cost_name'] = '建渣清运费';
                    break;
                case 12:
                    $data[$j]['cost_name'] = '企业微信报销';
                    break;
                case 13:
                    $data[$j]['cost_name'] = '库存盘点差异';
                    break;
                case 14:
                    $data[$j]['cost_name'] = '储备店长提成';

                    if (time() < 1617206400) {

                        $op = Financial::quan($this->us['store_id'], $start, $end);
                    } else {

                        $ReserveWage                   = new ReserveWage();
                        $orderList['CommissionAmount'] = 0;
                        if ($this->us['induction_time'] != 0) {

                            $orderList = $ReserveWage->ReserveManager($this->us['induction_time'], $start, $end, 2, $this->us['store_id']);
                        }

                        $op['TotalProfit'] = $orderList['CommissionAmount'];

                    }
                    $data[$j]['money'] = $op['TotalProfit'];
                    break;
                case 15:
                    $where                 = [
                        'order.notcost_time' => [['EXP', Db::raw(' IS NULL ')], ['eq', 0], 'or'],
                    ];
                    $data[$j]['cost_name'] = '订单费用';
                    $count                 = db('order')
                        ->join('user', 'user.user_id=order.assignor', 'left')
                        ->where('user.store_id', $this->us['store_id'])
                        ->where('user.ce', '<>', 2)
                        ->where($where)
                        ->whereBetween('order.created_time', [$start, $end])
                        ->count();
                    $fei=OrderModel::orderMoney($start);
                    $data[$j]['money']     = $count * $fei;
                    break;
                case 16:
                    $data[$j]['cost_name'] = '师傅人工提成';
                    $order_id              = db('order')
                        ->join('user', 'user.user_id=order.assignor', 'left')
                        ->whereBetween('order.cleared_time', [$start, $end])
                        ->where('user.store_id', $this->us['store_id'])
//                        ->where('user.ce', '<>', 2)
                        ->column('order_id');

                    $personal_price = Db::connect(config('database.db2'))->table('app_user_order_capital')->join('app_user', 'app_user.id=app_user_order_capital.user_id', 'left')->whereIn('app_user_order_capital.order_id', $order_id)->join(config('database.database') . '.capital', 'capital.capital_id=app_user_order_capital.capital_id', 'left')->where(['capital.types' => 1, 'capital.enable' => 1, 'capital.agency' => 0])->whereNull('app_user_order_capital.deleted_at')->sum('personal_price');
                    if ($start <= 1640966400) {

                        $order_idS = db('order')
                            ->join('user', 'user.user_id=order.assignor', 'left')
                            ->where('user.store_id', $this->us['store_id'])
                            ->where('master_half_cost_time', 'between time', [$start, $end])
                            ->where('cleared_time is null or cleared_time <' . $start . ' or cleared_time>' . $end)//排除本月结算的订单
                            ->column('order_id');

                        $half_price = Db::connect(config('database.db2'))->table('app_user_order_capital')->join('app_user', 'app_user.id=app_user_order_capital.user_id', 'left')->whereIn('app_user_order_capital.order_id', $order_idS)->join(config('database.database') . '.capital', 'capital.capital_id=app_user_order_capital.capital_id', 'left')->where(['capital.types' => 1, 'capital.enable' => 1, 'capital.agency' => 0])->whereNull('app_user_order_capital.deleted_at')->sum('half_price');
                    } else {
                        $half_price = 0;
                    }


                    $data[$j]['money'] = $personal_price - $half_price;
                    break;
                default:
                    $data[$j]['cost_name'] = '师傅人工提成（完工未完款提成50%）';
                    $order_id              = db('order')
                        ->join('user', 'user.user_id=order.assignor', 'left')
                        ->whereBetween('order.master_half_cost_time', [$start, $end])
                        ->where(function ($query) use ($start, $end) {
                            $query->whereNull('order.cleared_time');
                        })
                        ->whereNull('order.cleared_time')
                        ->where('user.store_id', $this->us['store_id'])
                        ->where('user.ce', '<>', 2)
                        ->column('order_id');
                    $capital               = db('capital')->whereIn('ordesr_id', $order_id)->where(['types' => 1, 'enable' => 1, 'agency' => 0])->column('capital_id');
                    if (!empty($capital)) {
                        $sql = "SELECT SUM(`half_price`) AS sum FROM app_user_order_capital AS a, (SELECT b.`user_id`, b.`order_id`,b.`capital_id`,MAX(b.created_at) AS `created_at` FROM app_user_order_capital AS b GROUP BY b.`user_id`,b.`order_id`,b.`capital_id`)AS c WHERE a.`user_id`=c.`user_id` AND a.`order_id`=c.`order_id` AND a.`capital_id`=c.`capital_id` AND a.created_at = c.created_at And a.`deleted_at` IS NULL AND a.`capital_id` in(" . implode(',', $capital) . ")";
                        //原生sql
                        $reality_artificial = Db::connect(config('database.db2'))->table('app_user_order_capital');
                        $sum                = $reality_artificial->query($sql);

                        if (isset($sum[0]['sum'])) {
                            $order_for_reality_artificial = sprintf('%.2f ', $sum[0]['sum']);
                        } else {
                            $order_for_reality_artificial = 0;
                        }
                    } else {
                        $order_for_reality_artificial = 0;
                    }
                    $data[$j]['money'] = $order_for_reality_artificial;
                    break;
            }

        }
        if ($inside == 1) {
            r_date(['data' => $data, 'count' => sprintf('%.2f ', array_sum(array_column($data, 'money')))], 200);
        } else {
            return array_sum(array_column($data, 'money'));
        }

    }

    /**
     * 提成
     */

    public static function Commission($store_id = 0, $user_id = 0, $start, $end)
    {

        $quer        = \db('order')
            ->join('user', 'order.assignor=user.user_id', 'left');
        $quer_agency = \db('order')
            ->join('user', 'order.assignor=user.user_id', 'left');
        if ($store_id != 0) {

            $quer->where('user.store_id', $store_id);
            $quer_agency->where('user.store_id', $store_id);

        } elseif ($user_id != 0) {

            $quer->where('user.user_id', $user_id);
            $quer_agency->where('user.user_id', $user_id);
        }

        $user   = $quer->where('user.reserve', 2)->where('user.ce', '<>', 2)->whereBetween('order.cleared_time', [$start, $end])->whereNotNull('order.received_time')->where('order.state', 7)->where('user.status', 0)->distinct(true)->column('order.order_id');
        $quer_a = $quer_agency->where('user.reserve', 2)->where('user.ce', '<>', 2)->whereBetween('order.settlement_time', [$start, $end])->distinct(true)->column('order.order_id');
        //代购主材金额
        $agency_capital      = db('capital')->whereIn('ordesr_id', $quer_a)->where(['types' => 1, 'enable' => 1, 'agency' => 1])->sum('to_price');
        $through             = db('capital')->whereIn('ordesr_id', $quer_a)->where(['types' => 1, 'enable' => 1, 'agency' => 1])->column('ordesr_id');
        $thro                = db('through')->whereIn('order_ids', $through)->where('baocun', 1)->field('through_id,order_ids')->select();
        $s1                  = array_column($thro, 'order_ids');
        $arr3                = array_diff($quer_a, $s1);
        $purchasing_discount = 0;
        $purchasing_expense  = 0;

        if ($thro) {

            $ca = db('envelopes')->whereIn('through_id', array_column($thro, 'through_id'))->field('sum(purchasing_discount) as purchasing_discount,sum(purchasing_expense) as purchasing_expense')->select();

            if ($ca) {
                foreach ($ca as $value) {
                    $purchasing_discount += $value['purchasing_discount'];
                    $purchasing_expense  += $value['purchasing_expense'];
                }
            }


        }

        if ($arr3) {
            $ca1 = db('envelopes')->whereIn('ordesr_id', $arr3)->where('through_id', 0)->field('sum(purchasing_discount) as purchasing_discount,sum(purchasing_expense) as purchasing_expense')->select();
            if ($ca1) {
                foreach ($ca1 as $value) {
                    $purchasing_discount += $value['purchasing_discount'];
                    $purchasing_expense  += $value['purchasing_expense'];
                }
            }


        }

        $agency = $agency_capital + $purchasing_expense - $purchasing_discount;//代购主材;
        //代购报销
        $agency_sum = db('reimbursement')->whereIn('order_id', $quer_a)->where(['status' => 1, 'classification' => 4])->sum('money');
        $data       = [];
        foreach ($user as $key => $item) {
            $amount                 = OrderModel::offer($item, 2);
            $data[$key]['order_id'] = $item;
            //完工完款金额除去主材
            $data[$key]['amount'] = $amount['amount'] - $amount['agency'];
            //人工费
            $capital = db('capital')->whereIn('ordesr_id', $item)->where(['types' => 1, 'enable' => 1, 'agency' => 0])->field('class_b,capital_id')->column('capital_id');
            if (!empty($capital)) {
                $sql = "SELECT SUM(`personal_price`) AS sum FROM app_user_order_capital AS a, (SELECT b.`user_id`, b.`order_id`,b.`capital_id`,MAX(b.created_at) AS `created_at` FROM app_user_order_capital AS b GROUP BY b.`user_id`,b.`order_id`,b.`capital_id`)AS c WHERE a.`user_id`=c.`user_id` AND a.`order_id`=c.`order_id` AND a.`capital_id`=c.`capital_id` AND a.created_at = c.created_at And a.`deleted_at` IS NULL AND a.`capital_id` in(" . implode(',', $capital) . ")";
                //原生sql
                $reality_artificial = Db::connect(config('database.db2'))->table('app_user_order_capital');
                $sum                = $reality_artificial->query($sql);
                if (isset($sum[0]['sum'])) {
                    $order_for_reality_artificial = round($sum[0]['sum'], 2);
                } else {
                    $order_for_reality_artificial = 0;
                }
            } else {
                $order_for_reality_artificial = 0;
            }
            $data[$key]['money'] = $order_for_reality_artificial;
            //材料报销
            $data[$key]['material_usage'] = sprintf('%.2f', db('material_usage')->where('status', 1)->where(['order_id' => $item])->sum('total_price'));
            //报销费用
            $data[$key]['reimbursement'] = db('reimbursement')->where(['order_id' => $item, 'status' => 1, 'classification' => ['<>', 4]])->sum('money');

        }


        $money['amounts']        = sprintf('%.2f', array_sum(array_column($data, 'amount')));
        $money['agency']         = sprintf('%.2f', $agency);
        $money['material_usage'] = sprintf('%.2f', array_sum(array_column($data, 'material_usage')));
        $money['reimbursement']  = sprintf('%.2f', array_sum(array_column($data, 'reimbursement')));
        $money['agency_sum']     = sprintf('%.2f', $agency_sum);
        $money['money']          = sprintf('%.2f', array_sum(array_column($data, 'money')));
        $count                   = ($money['amounts'] + $money['agency'] - $money['material_usage'] - $money['reimbursement'] - $money['agency_sum'] - $money['money']) * 0.02;

        return ['count' => sprintf('%.2f', $count), 'date' => $data, 'money' => $money];

    }

    /*
     * 材料预算
     */
    public static function budget($order_id)
    {
        $material   = 0;
        $labor_cost = db('capital')
            ->where('ordesr_id', $order_id)
            ->where('types', 1)
            ->where('enable', 1)
            ->where('agency', 0)
            ->field('projectId,square,material')
            ->select();
        foreach ($labor_cost as $k) {
            $material += $k['material'];
        }
        return $material;
    }

    /*
     * 订单费用
     */
    public function OrderFee()
    {
        $params = \request()->get();
        $start  = strtotime(date('Y-m-01 00:00:00', strtotime($params['startTime'])));//获取指定月份的第一天
        $end    = strtotime(date('Y-m-t 23:59:59', strtotime($params['startTime']))); //获取指定月份的最后一天
        //计入费用
        $where       = [
            'order.notcost_time' => [['EXP', Db::raw(' IS NULL ')], ['eq', 0], 'or'],
        ];
        $IncludediIn = db('order')
            ->join('user', 'user.user_id=order.assignor', 'left')
            ->join('contract co', 'order.contract_id=co.contract_id', 'left')
            ->where('user.store_id', $this->us['store_id'])
            ->where($where)
            ->where('user.ce', '<>', 2)
            ->whereBetween('order.created_time', [$start, $end])
            ->count();

        //不计入费用
        $wheres         = [
            'order.notcost_time' => [['EXP', Db::raw('IS NOT NULL')], ['neq', 0], 'and'],
        ];
        $IncludediNotIn = db('order')
            ->join('user', 'user.user_id=order.assignor', 'left')
            ->join('contract co', 'order.contract_id=co.contract_id', 'left')
            ->where('user.store_id', $this->us['store_id'])
            ->where($wheres)
            ->where('user.ce', '<>', 2)
            ->whereBetween('order.created_time', [$start, $end])
            ->count();
        $fei=OrderModel::orderMoney($start);
        r_date(['IncludediIn' => $IncludediIn . '个/￥' . $IncludediIn * $fei, 'IncludediNotIn' => $IncludediNotIn . '个/￥' . $IncludediNotIn * $fei, 'count' => $IncludediIn * $fei + $IncludediNotIn * $fei], 200);
    }

    /*
     *订单费用列表
     */

    public function OrderFeeList(OrderModel $orderModel)
    {
        $params = \request()->get();

        $m = $orderModel->table('order')
            ->alias('a')
            ->field('concat(p.province,c.city,u.county,a.addres) as  addres,a.contacts,co.con_time,a.order_id,a.state,go.title,a.assignor')
            ->join('province p', 'a.province_id=p.province_id', 'left')
            ->join('city c', 'a.city_id=c.city_id', 'left')
            ->join('county u', 'a.county_id=u.county_id', 'left')
            ->join('user us', 'a.assignor=us.user_id', 'left')
            ->join('goods_category go', 'a.pro_id=go.id', 'left')
            ->join('contract co', 'a.contract_id=co.contract_id', 'left');
        switch ($params['type']) {
            //计入
            case 1:
                $where = [
                    'a.notcost_time' => [['EXP', Db::raw(' IS NULL ')], ['eq', 0], 'or'],
                ];

                $m->where($where);
                break;
            //不计入
            case 2:
                $wheres = [
                    'a.notcost_time' => [['EXP', Db::raw('IS NOT NULL')], ['neq', 0], 'and'],
                ];
                $m->where($wheres);
                break;
        }
        $start=0;
        if (isset($params['startTime']) && $params['startTime'] != '') {
            $start = strtotime(date('Y-m-01 00:00:00', strtotime($params['startTime'])));//获取指定月份的第一天
            $end   = strtotime(date('Y-m-t 23:59:59', strtotime($params['startTime']))); //获取指定月份的最后一天
            $m->whereBetween('a.created_time', [$start, $end]);
        }
        $fei=OrderModel::orderMoney($start);
        $list = $list = $m->where('us.store_id', $this->us['store_id'])->where('us.ce', '<>', 2)->select();

        r_date(['count' => count($list), 'money' => count($list) * $fei, 'data' => $list], 200);

    }

    /*
     * 师傅人工提成
     */
    public function ArtificialCommission(OrderModel $orderModel)
    {
        $params = \request()->get();
        $start  = strtotime(date('Y-m-01 00:00:00', strtotime($params['startTime'])));//获取指定月份的第一天
        $end    = strtotime(date('Y-m-t 23:59:59', strtotime($params['startTime']))); //获取指定月份的最后一天
        $limit  = isset($params['limit']) ? $params['limit'] : 20;
        $m      = db('order')
            ->alias('a')
            ->field('concat(p.province,c.city,u.county,a.addres) as  addres,a.contacts,co.con_time,a.order_id,a.state,go.title,a.assignor,a.cleared_time,a.master_half_cost_time')
            ->join('province p', 'a.province_id=p.province_id', 'left')
            ->join('city c', 'a.city_id=c.city_id', 'left')
            ->join('county u', 'a.county_id=u.county_id', 'left')
            ->join('user us', 'a.assignor=us.user_id', 'left')
            ->join('goods_category go', 'a.pro_id=go.id', 'left')
            ->join('contract co', 'a.order_id=co.orders_id', 'left');

        if ($params['type50'] == 5) {
            $m->whereBetween('a.master_half_cost_time', [$start, $end])
                ->where(function ($query) use ($start, $end) {
                    $query->whereNull('a.cleared_time')->whereOr(['a.cleared_time' => ['NotBetween', [$start, $end]]]);
                })
//                ->whereNull('a.cleared_time')
                ->order('a.master_half_cost_time desc');

        } else {
            switch ($params['type']) {
                //签约时间
                case 1:
                    $m->whereBetween('co.con_time', [$start, $end]);
                    $m->order('co.con_time desc');
                    break;
                //完工时间
                case 2:
                    $m->whereBetween('a.finish_time', [$start, $end]);
                    $m->order('a.finish_time desc');
                    break;
                //完款时间
                case 3:
                    $m->whereBetween('a.received_time', [$start, $end]);
                    $m->order('a.received_time desc');
                    break;
                //结算时间
                case 4:
                    $m->whereBetween('a.cleared_time', [$start, $end]);
                    $m->order('a.cleared_time desc');
                    break; //结算时间

            }
        }
        if ($this->us['reserve'] == 2) {
            $m->where('a.assignor', $this->us['user_id']);
        } else {
            $m->where('us.store_id', $this->us['store_id']);
        }
        $list = $m->where('us.ce', '<>', 2)->select();


        foreach ($list as $k => $key) {
            $app_orders = [];
            //签约时间
            $list[$k]['con_time'] = !empty($key['con_time']) ? date('Y-m-d H:i:s', $key['con_time']) : '';

            $app_order = Db::connect(config('database.db2'))->table('app_user_order');
            if (isset($params['user_id']) && $params['user_id'] != 0) {
                $app_order->where('user_id', $params['user_id']);
            }

            $app_user_order = $app_order->where('order_id', $key['order_id'])->whereNull('deleted_at')->select();

            foreach ($app_user_order as $value => $item) {

                $personal_price = Db::connect(config('database.db2'))->table('app_user_order_capital')->join('app_user', 'app_user.id=app_user_order_capital.user_id', 'left')->where('app_user_order_capital.order_id', $item['order_id'])->join(config('database.database') . '.capital', 'capital.capital_id=app_user_order_capital.capital_id', 'left')->where(['capital.types' => 1, 'capital.enable' => 1, 'capital.agency' => 0])->where('app_user_order_capital.user_id', $item['user_id'])->whereNull('app_user_order_capital.deleted_at')->field('sum(app_user_order_capital.personal_price) as personal_price,sum(app_user_order_capital.half_price) as half_price,app_user.username')->select();

                if (!empty($personal_price[0]['username'])) {
                    if ($params['type50'] == 5) {
                        $app_orders[$value]['money'] = sprintf('%.2f', array_sum(array_column($personal_price, 'half_price')));

                    } else {
                        $half_price = Db::connect(config('database.db2'))->table('app_user_order_capital')->join('app_user', 'app_user.id=app_user_order_capital.user_id', 'left')->where('app_user_order_capital.order_id', $item['order_id'])->join(config('database.database') . '.capital', 'capital.capital_id=app_user_order_capital.capital_id', 'left')->where(['capital.types' => 1, 'capital.enable' => 1, 'capital.agency' => 0])->where('app_user_order_capital.user_id', $item['user_id'])->whereNull('app_user_order_capital.deleted_at')->field('sum(app_user_order_capital.half_price) as half_price')->sum('half_price');
                        $m1         = !empty($key['cleared_time']) ? date('Ym', $key['cleared_time']) : -1;
                        $m2         = !empty($key['master_half_cost_time']) ? date('Ym', $key['master_half_cost_time']) : -2;

                        if ($m1 == $m2) {
                            $app_orders[$value]['money'] = sprintf('%.2f', array_sum(array_column($personal_price, 'personal_price')));
                        } else {
                            $app_orders[$value]['money'] = sprintf('%.2f', array_sum(array_column($personal_price, 'personal_price')) - $half_price);
                        }


                    }
                    $app_orders[$value]['username'] = !empty($personal_price[0]['username']) ? $personal_price[0]['username'] : 0;
                }

                if (empty($app_orders)) {
                    unset($list[$k]);
                } else {

                    $list[$k]['data'] = array_values($app_orders);

                }

            }


        }

        $count = 0;
        foreach ($list as $l) {
            if (!empty($l['data'])) {
                foreach ($l['data'] as $k) {
                    $count += $k['money'];
                }
            }


        }

        r_date(['data' => array_values($list), 'count' => sprintf('%.2f', $count)], 200);

    }

    /*
     * 储备店长提成
     */
    public function ReserveManager()
    {
        $params     = \request()->get();
        $start      = strtotime(date('Y-m-01 00:00:00', strtotime($params['startTime'])));//获取指定月份的第一天
        $end        = strtotime(date('Y-m-t 23:59:59', strtotime($params['startTime']))); //获取指定月份的最后一天
        $proportion = 0;
        if ($this->us['reserve'] == 2 || isset($params['user_id']) && $params['user_id'] != 0) {
            $data = Financial::Commission(0, $params['user_id'], $start, $end)['money'];
            if ($data['amounts'] <= 50000) {
                $proportion = 0.02;
            } elseif (100000 >= $data['amounts'] && $data['amounts'] >= 50000) {
                $proportion = 0.03;
            } elseif (200000 >= $data['amounts'] && $data['amounts'] >= 100000) {
                $proportion = 0.04;
            } elseif ($data['amounts'] >= '200000') {
                $proportion = 0.05;
            }
            if ($data['amounts'] + $data['agency'] == 0) {
                $li = 0;
            } else {
                $li = sprintf('%.2f', ($data['amounts'] + $data['agency'] - $data['material_usage'] - $data['reimbursement'] - $data['agency_sum'] - $data['money']) / ($data['amounts'] + $data['agency'])) * 100;
            }
            //订单提成
            $TotalCommission = sprintf('%.2f', $data['amounts'] * $proportion);
            if ($TotalCommission < 0) {
                $proportion = 0;
            }
            //主材提成
            $AmountOfCommission = sprintf('%.2f', ($data['agency'] - $data['agency_sum']) * 0.1);
            //利润率

            $TotalProfit = sprintf('%.2f', $TotalCommission + $AmountOfCommission);
        } else {
            $data               = $this::quan($this->us['store_id'], $start, $end);
            $TotalCommission    = $data['TotalProfit'];
            $li                 = $data['li'];
            $TotalProfit        = sprintf('%.2f', $data['TotalProfit'] + $data['AmountOfCommission']);
            $AmountOfCommission = $data['AmountOfCommission'];

            if ($data['amounts'] == 0) {
                $proportion = 0;
            } else {
                $proportion = $data['TotalProfit'] / $data['amounts'];
            }


        }
        if (isset($params['kk'])) {
            res_date(['datta' => $data, 'TotalCommission' => $TotalCommission, 'ti' => (sprintf('%.2f', $proportion) * 100) . '%', 'ti1' => '10%', 'proportion' => $li . '%', 'AmountOfCommission' => $TotalProfit, 'TotalProfit' => $AmountOfCommission,], 200);
        } else {
            r_date(['datta' => $data, 'TotalCommission' => $TotalCommission, 'ti' => (sprintf('%.2f', $proportion) * 100) . '%', 'ti1' => '10%', 'proportion' => $li . '%', 'AmountOfCommission' => $TotalProfit, 'TotalProfit' => $AmountOfCommission,], 200);
        }


    }

    /*
    * 全部提成合计
    */
    public static function quan($store_id, $start, $end)
    {

        $quer = \db('user')->where('store_id', $store_id)->where('reserve', 2)->where('ce', '<>', 2)->column('user_id');

        $date = [];
        foreach ($quer as $k => $value) {
            $data       = Financial::Commission(0, $value, $start, $end)['money'];
            $proportion = 0;
            if ($data['amounts'] <= 50000) {
                $proportion = 0.02;
            } elseif (100000 >= $data['amounts'] && $data['amounts'] >= 50000) {
                $proportion = 0.03;
            } elseif (200000 >= $data['amounts'] && $data['amounts'] >= 100000) {
                $proportion = 0.04;
            } elseif ($data['amounts'] >= '200000') {
                $proportion = 0.05;
            }

            //订单提成
            $TotalCommission = sprintf('%.2f', $data['amounts'] * $proportion);

            //主材提成
            $AmountOfCommission = sprintf('%.2f', ($data['agency'] - $data['agency_sum']) * 0.1);
            //利润率
            $TotalProfit                 = sprintf('%.2f', $TotalCommission + $AmountOfCommission);
            $date[$k]['TotalCommission'] = $proportion;
            //材料报销
            $date[$k]['AmountOfCommission'] = $AmountOfCommission;
            //报销费用
            $date[$k]['TotalProfit']    = $TotalProfit;
            $date[$k]['amounts']        = $data["amounts"];
            $date[$k]["agency"]         = $data["agency"];
            $date[$k]["material_usage"] = $data["material_usage"];
            $date[$k]["reimbursement"]  = $data["reimbursement"];
            $date[$k]["agency_sum"]     = $data["agency_sum"];
            $date[$k]["money"]          = $data["money"];
        }
        $money['TotalProfit']        = sprintf('%.2f', array_sum(array_column($date, 'TotalProfit')));
        $money['TotalCommission']    = sprintf('%.2f', array_sum(array_column($date, 'TotalCommission')));
        $money['AmountOfCommission'] = sprintf('%.2f', array_sum(array_column($date, 'AmountOfCommission')));
        $money['amounts']            = sprintf('%.2f', array_sum(array_column($date, 'amounts')));
        $money['agency']             = sprintf('%.2f', array_sum(array_column($date, 'agency')));
        $money['material_usage']     = sprintf('%.2f', array_sum(array_column($date, 'material_usage')));
        $money['reimbursement']      = sprintf('%.2f', array_sum(array_column($date, 'reimbursement')));
        $money['agency_sum']         = sprintf('%.2f', array_sum(array_column($date, 'agency_sum')));
        $money['money']              = sprintf('%.2f', array_sum(array_column($date, 'money')));

        if ($money['amounts'] + $money['agency'] == 0) {
            $money['li'] = 0;
        } else {
            $money['li'] = sprintf('%.2f', ($money['amounts'] + $money['agency'] - $money['material_usage'] - $money['reimbursement'] - $money['agency_sum'] - $money['money']) / ($money['amounts'] + $money['agency'])) * 100;
        }
        return $money;


    }

    /*
     * 店长分红
     */
    public function StoreManagerBonus(OrderModel $orderModel)
    {
        $params        = \request()->get();
        $start         = strtotime(date('Y-m-01 00:00:00', strtotime($params['startTime'])));//获取指定月份的第一天
        $end           = strtotime(date('Y-m-t 23:59:59', strtotime($params['startTime']))); //获取指定月份的最后一天
        $main_payment  = 0;
        $agent_payment = 0;
        if ($start >= 1640966400) {
            $newList            = $this->NewAlgorithm($start, $end, $params['startTime']);
            $TotalCommission    = $newList['TotalCommission'];
            $AmountOfCommission = $newList['proportion'];
            $TotalProfit        = $newList['AmountOfCommission'];
            $proportion         = $newList['TotalProfit'];
            $da                 = $newList['datta'];
        } else {

            $io = db('payment')->join('order', 'order.order_id=payment.orders_id', 'left')->join('user', 'order.assignor=user.user_id', 'left')->where('user.store_id', $this->us['store_id'])->whereBetween('payment.uptime', [$start, $end])->where('payment.cleared_time', '>', 0)->field('payment.money,payment.weixin,payment.success,payment.material,payment.agency_material')->select();
            foreach ($io as $value) {
                if ($value['weixin'] != 2) {
                    if ($value['agency_material'] == 0 && $value['material'] == 0) {
                        $main_payment += $value['money'];
                    }
                    if ($value['material'] != 0) {
                        $main_payment += $value['material'];
                    }
                }
                if ($value['weixin'] == 2 && $value['success'] == 2) {
                    if ($value['agency_material'] == 0 && $value['material'] == 0) {
                        $main_payment += $value['money'];
                    }
                    if ($value['material'] != 0) {
                        $main_payment += $value['material'];
                    }
                }
            }

            $io1 = db('payment')->join('order', 'order.order_id=payment.orders_id', 'left')->join('user', 'order.assignor=user.user_id', 'left')->where('user.store_id', $this->us['store_id'])->whereBetween('order.settlement_time', [$start, $end])->field('payment.money,payment.weixin,payment.success,payment.material,payment.agency_material')->select();
            foreach ($io1 as $value) {
                if ($value['weixin'] != 2) {
                    if ($value['agency_material'] != 0) {
                        $agent_payment += $value['agency_material'];
                    }

                }
                if ($value['weixin'] == 2 && $value['success'] == 2) {
                    if ($value['agency_material'] != 0) {
                        $agent_payment += $value['agency_material'];
                    }
                }
            }


            //人工费用
//        $list          =$orderModel->where('assignor', $this->us['user_id'])->whereBetween('received_time', [$start, $end])->where('state', 7)->distinct(true)->column('order_id');
////        $app_user_order=Db::connect(config('database.db2'))->table('app_user_order')->whereIn('order_id', $list)->whereNull('deleted_at')->column('order_id');
//        $app_orders=Db::connect(config('database.db2'))->table('app_user_order_capital')->join('app_user', 'app_user.id=app_user_order_capital.user_id', 'left')->whereIn('app_user_order_capital.order_id',$list)->whereNull('app_user_order_capital.deleted_at')->sum('app_user_order_capital.personal_price');
            $da['material']        = (sprintf('%.2f', $main_payment));
            $da['agency_material'] = (sprintf('%.2f', $agent_payment));
//        $da['money']               =$app_orders;

            $da['material_usage'] = sprintf('%.2f', db('material_usage')->join('user', 'material_usage.shopowner_id=user.user_id', 'left')->where('user.store_id', $this->us['store_id'])->where('material_usage.status', 1)->whereBetween('material_usage.adopt', [$start, $end])->sum('total_price'));

            $da['reimbursement'] = sprintf('%.2f', db('reimbursement')->join('user', 'reimbursement.shopowner_id=user.user_id', 'left')->where('user.store_id', $this->us['store_id'])->whereBetween('reimbursement.adopt', [$start, $end])->where(['reimbursement.status' => 1, 'reimbursement.classification' => ['<>', 4]])->sum('money'));

            $da['agency_reimbursement'] = sprintf('%.2f', db('reimbursement')->join('user', 'reimbursement.shopowner_id=user.user_id', 'left')->join('order', 'reimbursement.order_id=order.order_id', 'left')->where('user.store_id', $this->us['store_id'])->where('user.ce', 'neq', 2)->whereBetween('order.settlement_time', [$start, $end])->where(['reimbursement.status' => 1, 'reimbursement.classification' => 4])->sum('money'));
            $da['shopExpenses']         = sprintf('%.2f', $this->shopExpenses(2, $params['startTime']));
            $proportion                 = 0;
//        if ($da['shopExpenses'] > 0) {
//            if ($da['shopExpenses'] && $da['shopExpenses'] <= 50000) {
//                $proportion=0.05;
//            } elseif (100000 >= $da['shopExpenses'] && $da['shopExpenses'] >= 50000){
//                $proportion=0.1;
//            } elseif ($da['shopExpenses'] && $da['shopExpenses'] >= 100000){
//                $proportion=0.2;
//            }
//        }

            $op              = Financial::quan($this->us['store_id'], $start, $end);
            $money           = $op['TotalProfit'];
            $TotalCommission = sprintf('%.2f', ($da['material'] + $da['agency_material'] - $da['shopExpenses'] - $da['material_usage'] - $da['reimbursement']) - $da['agency_reimbursement']);
            if ($start < 1640966400) {
                if ($TotalCommission > 0) {
                    if ($TotalCommission && $TotalCommission <= 50000) {
                        $proportion = 0.05;
                    } elseif (100000 >= $TotalCommission && $TotalCommission >= 50000) {
                        $proportion = 0.1;
                    } elseif ($TotalCommission && $TotalCommission >= 100000) {
                        $proportion = 0.2;
                    }
                }
            } else {
                $proportion = 0.1;
            }


            if ($TotalCommission < 0) {
                $proportion = 0;
            }
            $TotalProfit = sprintf('%.2f', $TotalCommission * $proportion);
            if ($da['material'] + $da['agency_material'] == 0) {
                $AmountOfCommission = 0;
            } else {
                $AmountOfCommission = (sprintf('%.2f', $TotalCommission / ($da['material'] + $da['agency_material'])) * 100) . '%';
            }
        }


        r_date(['datta' => $da, 'TotalCommission' => $TotalCommission, 'proportion' => $AmountOfCommission, 'AmountOfCommission' => $TotalProfit, 'TotalProfit' => (sprintf('%.2f', $proportion) * 100) . '%', 'ti1' => '10%'], 200);
    }

    public function NewAlgorithm($start, $end, $startTime)
    {
        $quer        = \db('order')
            ->join('user', 'order.assignor=user.user_id', 'left');
        $quer_agency = \db('order')
            ->join('user', 'order.assignor=user.user_id', 'left');
        $quer->where('user.store_id', $this->us['store_id']);
        $quer_agency->where('user.store_id', $this->us['store_id']);
        $user =
//            $quer->where('user.reserve', 2)
//            ->where('user.ce', '<>', 2)
            $quer->whereBetween('order.cleared_time', [$start, $end])->whereNotNull('order.received_time')->where('order.state', 7)->where('user.status', 0)->distinct(true)->column('order.order_id');

        $quer_a = $quer_agency
//            ->where('user.ce', '<>', 2)
            ->whereBetween('order.settlement_time', [$start, $end])->distinct(true)->column('order.order_id');

        $mainOrder_id = $user;
        $order_id     = $quer_a;
        $io           = db('payment')->join('order', 'order.order_id=payment.orders_id', 'left')->whereIn('order.order_id', $mainOrder_id)->where('payment.uptime', '>', 1625068800)->where('payment.cleared_time', '>', 0)->field('payment.money,payment.weixin,payment.success,payment.material,payment.agency_material')->select();

        $io1 = db('payment')->join('order', 'order.order_id=payment.orders_id', 'left')->where('payment.cleared_time', '>', 0)->whereIn('order.order_id', $order_id)->field('payment.money,payment.weixin,payment.success,payment.material,payment.agency_material')->select();

        $main_payment  = 0;
        $agent_payment = 0;
        foreach ($io as $value) {
            if ($value['weixin'] != 2) {
                if ($value['agency_material'] == 0 && $value['material'] == 0) {
                    $main_payment += $value['money'];
                }
                if ($value['material'] != 0) {
                    $main_payment += $value['material'];
                }
            }
            if ($value['weixin'] == 2 && $value['success'] == 2) {
                if ($value['agency_material'] == 0 && $value['material'] == 0) {
                    $main_payment += $value['money'];
                }
                if ($value['material'] != 0) {
                    $main_payment += $value['material'];
                }
            }
        }
        foreach ($io1 as $value) {
            if ($value['weixin'] != 2) {
                if ($value['agency_material'] != 0) {
                    $agent_payment += $value['agency_material'];
                }

            }
            if ($value['weixin'] == 2 && $value['success'] == 2) {
                if ($value['agency_material'] != 0) {
                    $agent_payment += $value['agency_material'];
                }
            }
        }

        $da['material']        = (sprintf('%.2f', $main_payment));
        $da['agency_material'] = (sprintf('%.2f', $agent_payment));

        $da['material_usage'] = sprintf('%.2f', db('material_usage')->whereIn('material_usage.order_id', $mainOrder_id)->where('adopt', '>', 1625155200)->where('material_usage.status', 1)->sum('total_price'));

        $da['reimbursement'] = sprintf('%.2f', db('reimbursement')->whereIn('reimbursement.order_id', $mainOrder_id)->where('adopt', '>', 1625155200)->where(['reimbursement.status' => 1, 'reimbursement.classification' => ['<>', 4]])->sum('money'));

        $da['agency_reimbursement'] = sprintf('%.2f', db('reimbursement')->whereIn('reimbursement.order_id', $order_id)->where(['reimbursement.status' => 1, 'reimbursement.classification' => 4])->sum('money'));
        $da['shopExpenses']         = sprintf('%.2f', $this->shopExpenses(2, $startTime));

        $proportion      = 0;
        $op              = Financial::quan($this->us['store_id'], $start, $end);
        $money           = $op['TotalProfit'];
        $TotalCommission = sprintf('%.2f', ($da['material'] + $da['agency_material'] - $da['shopExpenses'] - $da['material_usage'] - $da['reimbursement']) - $da['agency_reimbursement']);


        $proportion = 0.1;
        if ($TotalCommission < 0) {
            $proportion = 0;
        }

        $TotalProfit = sprintf('%.2f', $TotalCommission * $proportion);
        if ($da['material'] + $da['agency_material'] == 0) {
            $AmountOfCommission = 0;
        } else {
            $AmountOfCommission = (sprintf('%.2f', $TotalCommission / ($da['material'] + $da['agency_material'])) * 100) . '%';
        }
        return ['datta' => $da, 'TotalCommission' => $TotalCommission, 'proportion' => $AmountOfCommission, 'AmountOfCommission' => $TotalProfit, 'TotalProfit' => sprintf('%.2f', $proportion), 'ti1' => '10%'];
    }

    /*
     * 主合同收款金额
     */
    public function CollectionOfMainContract()
    {
        $param = Request::instance()->get();
        $start = strtotime(date('Y-m-01 00:00:00', strtotime($param['startTime'])));//获取指定月份的第一天
        $end   = strtotime(date('Y-m-t 23:59:59', strtotime($param['startTime']))); //获取指定月份的最后一天
        //排除测试店长
        if ($start >= 1640966400) {
            $data = $this->NewCollectionOfMainContract($start, $end,$param['page'], $param['limit']);

        } else {
            $order_id = $this->testStoreManageOrderId($param);

            //收款列表
            $_payment = db('payment');
            $_payment->join('order order', 'order.order_id=payment.orders_id', 'left');
            //$_payment->where('payment.agency_material','=',0);
            $_payment->where('payment.money', '>', 0);
            $_payment->where('payment.cleared_time', '>', 0);
            if (count($order_id['order']) > 0) {
                $_payment->whereNotIn('orders_id', $order_id['order']);
            }
//        $_payment->whereRaw('IF(payment.weixin=2 && payment.success=2,payment.success=1)');
            $l[] = ['exp', Db::raw("IF(payment.weixin=2,payment.success=2,payment.success=1)")];
            $_payment->where($l);

            $_payment->where('payment.uptime', 'between time', [$start, $end]);//旧算法，以收款时间为记
            //$_payment->where('payment.cleared_time','between time',[$param['begin'],$param['end']]);//新算法，以入账时间为记
            $_payment->whereIn('order.assignor', $order_id['user_id']);
            $_payment->field('order.addres as title,payment.weixin,payment.success,payment.money as mon,payment.material,payment.uptime,payment.cleared_time,order.order_id as id,order.assignor as user_id');
            $data = $_payment->page($param['page'], $param['limit'])->select();

            foreach ($data as $key => $value) {
                //$data['data'][$key]['title']=payment_type($value['weixin']);
                $data[$key]['time'] = date('Y-m-d H:i:s', $value['uptime']);
                if ($value['weixin'] == 2 && $value['success'] != 2) {
                    $value['mon'] = 0;
                }
                if ($value['material'] == 0 && $value['mon'] > 0) {
                    $data[$key]['money'] = $value['mon'];
                } else {
                    $data[$key]['money'] = $value['material'];
                }
            }
        }
        r_date(['datta' => array_values($data)], 200);
    }

    /*
     * 主合同收款金额
     */
    public function NewCollectionOfMainContract($start,$end,$page,$limit)
    {
        //排除测试店长

        $quer = \db('order')->join('user', 'order.assignor=user.user_id', 'left');
        $quer->where('user.store_id', $this->us['store_id']);

        $user = $quer->whereBetween('order.cleared_time', [$start, $end])->whereNotNull('order.received_time')->where('order.state', 7)->where('user.status', 0)->distinct(true)->column('order.order_id');

        $_payment = db('payment');
        $_payment->join('order order', 'order.order_id=payment.orders_id', 'left');
        $_payment->where('payment.cleared_time', '>', 0);
        $_payment->where('payment.uptime', '>', 1625068800);

        $_payment->whereIn('payment.orders_id', $user);

        $l[] = ['exp', Db::raw("IF(payment.weixin=2,payment.success=2,payment.success=1)")];
        $_payment->where($l);
        //$_payment->where('payment.cleared_time','between time',[$param['begin'],$param['end']]);//新算法，以入账时间为记

        $_payment->field('order.addres as title,payment.weixin,payment.success,payment.money as mon,payment.material,payment.uptime,payment.cleared_time,order.order_id as id,order.assignor as user_id');
        $data = $_payment->page($page,$limit)->select();

        foreach ($data as $key => $value) {
            //$data['data'][$key]['title']=payment_type($value['weixin']);
            $data[$key]['time'] = date('Y-m-d H:i:s', $value['uptime']);
            if ($value['weixin'] == 2 && $value['success'] != 2) {
                $value['mon'] = 0;
            }
            if ($value['material'] == 0 && $value['mon'] > 0) {
                $data[$key]['money'] = $value['mon'];
            } else {
                $data[$key]['money'] = $value['material'];
            }
        }
        return $data;

    }

//代购合同收款金额
    public function storeNodeAgent()
    {
        $param = Request::instance()->get();
        $start = strtotime(date('Y-m-01 00:00:00', strtotime($param['startTime'])));//获取指定月份的第一天
        $end   = strtotime(date('Y-m-t 23:59:59', strtotime($param['startTime']))); //获取指定月份的最后一天
        //排除测试店长
        $order_id = $this->testStoreManageOrderId($param);
        //收款列表
        $_payment = db('payment');
        $_payment->join('order order', 'order.order_id=payment.orders_id', 'left');
        $_payment->where('payment.agency_material', '>', 0);
        $_payment->where('payment.money', '>', 0);
        if (count($order_id['order']) > 0) {
            $_payment->whereNotIn('orders_id', $order_id['order']);
        }
        $l[] = ['exp', Db::raw("IF(payment.weixin=2,payment.success=2,payment.success=1)")];
        $_payment->where($l);
//        $_payment->where('payment.uptime', 'between time', [$start, $end]);//旧算法，以收款时间为记
        $_payment->where('order.settlement_time', 'between time', [$start, $end]);//新算法，以入账时间为记
        $_payment->whereIn('order.assignor', $order_id['user_id']);
        $_payment->field('order.addres as title,payment.weixin,payment.success,payment.agency_material,payment.uptime,payment.cleared_time,order.order_id as id,order.assignor as user_id');
        $data = $_payment->page($param['page'], $param['limit'])->select();

        foreach ($data as $key => $value) {
            //$data['data'][$key]['title']=payment_type($value['weixin']);
            $data[$key]['time'] = date('Y-m-d H:i:s', $value['uptime']);
            if ($value['weixin'] == 2 && $value['success'] != 2) {
                $value['agency_material'] = 0;
            }
            $data[$key]['money'] = $value['agency_material'];
        }
        r_date(['datta' => array_values($data)], 200);
    }

//材料费用
    public
    function storeNodeMaterial()
    {
        $param           = Request::instance()->get();
        $order_id        = $this->testStoreManageOrderId($param);
        $start           = strtotime(date('Y-m-01 00:00:00', strtotime($param['startTime'])));//获取指定月份的第一天
        $end             = strtotime(date('Y-m-t 23:59:59', strtotime($param['startTime']))); //获取指定月份的最后一天
        $_material_usage = db('material_usage')->alias('mu');
        $_material_usage->whereIn('shopowner_id', $order_id['user_id']);
        $_material_usage->where('status', '=', 1);//状态是通过审核
        $_material_usage->where('adopt', 'between time', [$start, $end]);//过审时间范围内的
        $_material_usage->field('mu.material_name as title,mu.adopt,mu.total_price as money,mu.order_id as id,mu.shopowner_id as user_id');
        $data = $_material_usage->page($param['page'], $param['limit'])->select();

        //dd($data);
        foreach ($data as $key => $value) {
            $data[$key]['time'] = date('Y-m-d H:i:s', $value['adopt']);
        }
        r_date(['datta' => array_values($data)], 200);
    }

//报销费用主材报销
    public
    function storeNodeReserveCost()
    {
        $param = Request::instance()->get();
        $user  = $this->testStoreManageOrderId($param);
        $start = strtotime(date('Y-m-01 00:00:00', strtotime($param['startTime'])));//获取指定月份的第一天
        $end   = strtotime(date('Y-m-t 23:59:59', strtotime($param['startTime']))); //获取指定月份的最后一天

        $_reimbursement = db('reimbursement');

        if ($param['is_material'] == 'true') {
            $_reimbursement->join('order', 'reimbursement.order_id=order.order_id', 'left')
                ->where('order.settlement_time', 'between time', [$start, $end])
                ->where('reimbursement.status', 1)
                ->where('reimbursement.classification', 4);
        } else {
            $_reimbursement->whereBetween('reimbursement.adopt', [$start, $end])->where(['reimbursement.status' => 1])->where('classification', '<>', 4);
        }

        $_reimbursement->whereIn('reimbursement.shopowner_id', $user['user_id'])->field('reimbursement.reimbursement_name as title,reimbursement.money,reimbursement.order_id as id,adopt,reimbursement.shopowner_id as user_id');
        $data = $_reimbursement->page($param['page'], $param['limit'])->select();

        foreach ($data as $key => $value) {
            $data[$key]['time'] = date('Y-m-d H:i:s', $value['adopt']);

        }
        r_date(['datta' => array_values($data)], 200);
    }

    public
    function testStoreManageOrderId($param)
    {
        $order_id = db('order')->join('user', 'order.assignor=user.user_id', 'left')->where('user.ce', 2)->column('order.order_id');
        if ($param['user_id'] == 0) {
            $store_id = $this->us['store_id'];
        } else {
            $store_id = \db('user')->where('user.ce', 'neq', 2)->where('user_id', $param['user_id'])->value('store_id');
        }
        $user_id = \db('user')->where('store_id', $store_id)->column('user_id');

        return ['order' => $order_id, 'user_id' => $user_id];
    }

    /*
     * 我的业绩
     */
    public
    function achievement(OrderModel $orderModel)
    {

        $params = \request()->get();
        $start  = strtotime(date('Y-m-01 00:00:00', strtotime($params['startTime'])));//获取指定月份的第一天
        $end    = strtotime(date('Y-m-t 23:59:59', strtotime($params['startTime']))); //获取指定月份的最后一天

        $m = $orderModel->table('order')
            ->alias('a')
            ->field('concat(p.province,c.city,u.county,a.addres) as  addres,a.contacts,co.con_time,a.order_id,a.state,a.assignor,go.title')
            ->join('province p', 'a.province_id=p.province_id', 'left')
            ->join('city c', 'a.city_id=c.city_id', 'left')
            ->join('county u', 'a.county_id=u.county_id', 'left')
            ->join('user us', 'a.assignor=us.user_id', 'left')
            ->join('goods_category go', 'a.pro_id=go.id', 'left')
            ->join('contract co', 'a.order_id=co.orders_id', 'left')
            ->where('a.assignor', $this->us['user_id']);

        switch ($params['type']) {
            //签约时间
            case 1:
                $m->whereBetween('co.con_time', [$start, $end]);
                $m->order('co.con_time desc');
//                $list=$m->where('us.ce', '<>', 2)->where('a.state', '<>', 9)->select();
                $list = $m->whereBetween('a.state', [4, 7])->select();

                break;
            //增项
            case 2:
                $list = $this->zeng($start, $end, $this->us['user_id']);
                break;
            //减项
            case 3:
                $list = $this->jian($start, $end, $this->us['user_id']);
                break;
            //退单
            default :
                $list = $this->tui($start, $end, $this->us['user_id']);

                break;
        }
        foreach ($list as $k => $key) {
            $data                 = [];
            $list[$k]['con_time'] = !empty($key['con_time']) ? date('Y-m-d H:i:s', $key['con_time']) : '';
            $mouth                = $orderModel->offer($key['order_id']);
            //签约金额
            $list[$k]['amount'] = sprintf('%.2f', $mouth['amount']);
            //代购主材
            $list[$k]['agency'] = sprintf('%.2f', $mouth['agency']);
            $da['ification']    = 2;
            $da['order_id']     = $key['order_id'];
            $data[]             = $da;
            //报销金额
            $list[$k]['agency_sum'] = sprintf('%.2f', db('reimbursement')->where(['order_id' => $key['order_id'], 'status' => 1, 'classification' => ['<>', 4]])->sum('money'));
            $capital                = $cap = db('capital')->where(['ordesr_id' => $key['order_id'], 'types' => 1, 'enable' => 1])->field('class_b,capital_id')->column('capital_id');
            if (!empty($capital)) {
                $sql = "SELECT SUM(`personal_price`) AS sum FROM app_user_order_capital AS a, (SELECT b.`user_id`, b.`order_id`,b.`capital_id`,MAX(b.created_at) AS `created_at` FROM app_user_order_capital AS b GROUP BY b.`user_id`,b.`order_id`,b.`capital_id`)AS c WHERE a.`user_id`=c.`user_id` AND a.`order_id`=c.`order_id` AND a.`capital_id`=c.`capital_id` AND a.created_at = c.created_at And a.`deleted_at` IS NULL AND a.`capital_id` in(" . implode(',', $capital) . ")";
                //原生sql
                $reality_artificial = Db::connect(config('database.db2'))->table('app_user_order_capital');
                $sum                = $reality_artificial->query($sql);
                if (isset($sum[0]['sum'])) {
                    $order_for_reality_artificial = round($sum[0]['sum'], 2);
                } else {
                    $order_for_reality_artificial = 0;
                }
            } else {
                $order_for_reality_artificial = 0;
            }
            //人工实际
            $list[$k]['estimate'] = sprintf('%.2f', $order_for_reality_artificial);
            //材料实际
            $list[$k]['MaterialBudget'] = sprintf('%.2f', db('material_usage')
                ->whereIn('order_id', $key['order_id'])
                ->where('status', 1)
                ->sum('total_price'));

            //利润
            $list[$k]['profit'] = sprintf('%.2f', $list[$k]['amount'] - $list[$k]['estimate'] - $list[$k]['MaterialBudget'] - $list[$k]['agency'] - $list[$k]['agency_sum']);


        }
        $amount = sprintf('%.2f', array_sum(array_column($list, 'amount')));
        $zeng   = array_sum(array_column($this->zeng($start, $end, $this->us['user_id']), 'turnover'));
        $jian   = array_sum(array_column($this->jian($start, $end, $this->us['user_id']), 'turnover'));
        $tui    = array_sum(array_column($this->tui($start, $end, $this->us['user_id']), 'turnover'));
        $ye     = sprintf('%.2f', $amount + $zeng - $jian - $tui);

        r_date(['zeng' => $zeng, 'jian' => $jian, 'tui' => $tui, 'amount' => $amount, 'ye' => $ye, 'data' => $list], 200);


    }

    public
    function zeng($start, $end, $user)
    {
        //查增项
        $increment = db('capital')
            ->join('order order', 'capital.ordesr_id = order.order_id', 'left')
            ->join('contract contract', 'contract.orders_id = capital.ordesr_id', 'left')
            ->join('province p', 'order.province_id=p.province_id', 'left')
            ->join('city c', 'order.city_id=c.city_id', 'left')
            ->join('county u', 'order.county_id=u.county_id', 'left')
            ->join('goods_category go', 'order.pro_id=go.id', 'left')
            ->join('user user', 'user.user_id=order.assignor', 'left')
            ->where('capital.increment', 1)//是增项
            ->where('capital.enable', 1)//是增项
            ->where('order.state', '>', 3)
            ->where('order.state', '<', 8)//成交
            ->where('contract.con_time', '<', $start)//在签约时间以前的增项
            ->where('capital.crtime', 'between time', [$start, $end])//在时间范围内的增项
            ->where('order.assignor', $user)//用户相关的
            ->field('order.order_id,user.user_id, user.store_id, SUM(to_price) as turnover,capital.crtime as times,contract.con_time,concat(p.province,c.city,u.county,order.addres) as  addres,order.contacts,go.title')
            ->group('capital.ordesr_id')
            ->select();

        return $increment;

    }

    public
    function jian($start, $end, $user)
    {
        //查减项
        $deduction = db('capital')
            ->join('order order', 'capital.ordesr_id = order.order_id', 'left')
            ->join('contract contract', 'contract.orders_id = capital.ordesr_id', 'left')
            ->join('user user', 'user.user_id=order.assignor', 'left')
            ->join('province p', 'order.province_id=p.province_id', 'left')
            ->join('city c', 'order.city_id=c.city_id', 'left')
            ->join('county u', 'order.county_id=u.county_id', 'left')
            ->join('goods_category go', 'order.pro_id=go.id', 'left')
            ->where('capital.untime', '>', 0)//是减项
            ->where('capital.enable', 1)//是增项
            ->where('order.state', '>', 3)
            ->where('order.state', '<', 8)//成交
            ->where('contract.con_time', '<', $start)//在签约时间以前的增项
            ->where('capital.untime', 'between time', [$start, $end])//减项在时间范围内
            ->where('order.assignor', $user)//用户相关的
            ->field('order.order_id,user.user_id,user.store_id,SUM(to_price) as turnover,capital.untime as times,contract.con_time,concat(p.province,c.city,u.county,order.addres) as  addres,order.contacts,go.title')
            ->group('capital.ordesr_id')
            ->select();

        return $deduction;
    }

    public
    function tui($start, $end, $user)
    {
        //查退单
        $deduction = db('capital')
            ->join('order order', 'capital.ordesr_id = order.order_id', 'left')
            ->join('province p', 'order.province_id=p.province_id', 'left')
            ->join('city c', 'order.city_id=c.city_id', 'left')
            ->join('county u', 'order.county_id=u.county_id', 'left')
            ->join('goods_category go', 'order.pro_id=go.id', 'left')
            ->join('contract contract', 'contract.orders_id = capital.ordesr_id', 'left')
            ->join('user user', 'user.user_id=order.assignor', 'left')
            ->join('envelopes envelopes', 'envelopes.envelopes_id=capital.envelopes_id', 'left')
            ->where('contract.con_time', '<', $start)//在签约时间以前的增项
            ->whereBetween('order.tui_time', [$start, $end])//在签约时间以前的增项
            ->where('order.state', 9)//在签约时间以前的增项
            ->where('order.assignor', $user)//用户相关的
            ->where('capital.types', 1)//用户相关的
            ->where('capital.enable', 1)//用户相关的
            ->field('order.order_id,user.user_id,user.store_id,(SUM(to_price)+envelopes.expense+envelopes.purchasing_expense-envelopes.give_money -envelopes.purchasing_discount )as turnover,capital.untime as times,contract.con_time,concat(p.province,c.city,u.county,order.addres) as  addres,order.contacts,go.title')
            ->group('capital.ordesr_id')
            ->select();
        return $deduction;
    }

    public
    function TurnoverRate($type = '')
    {

        $params = \request()->get();
        if (!isset($params['startTime'])) {
            $startTime = time();
        } else {
            $startTime = strtotime($params['startTime']);
        }
        $start = strtotime(date('Y-m-01 00:00:00', $startTime));//获取指定月份的第一天
        $end   = strtotime(date('Y-m-t 23:59:59', $startTime)); //获取指定月份的最后一天
        //成交量
        $g1        = db('contract')->join('order', 'order.order_id=contract.orders_id', 'left')->whereBetween('order.state', [4, 7])->whereBetween('contract.con_time', [$start, $end])
            // ->where('contract.type', 1)
            ->where('order.assignor', $this->us['user_id'])->field('contract.orders_id,order.created_time,order.channel_details')->select();
        $order_ids = [];
        $order     = [];
        foreach ($g1 as $key => $val) {
            $order_ids[] = $val['orders_id'];
            if ($val['created_time'] < $start) {
                $order[] = $val['orders_id'];
            }

        }

        $date['cheng'] = count($g1);

        //接单量
        $date['jie'] = db('order')->where(['assignor' => $this->us['user_id'], 'created_time' => ['between', [$start, $end]]])->count();

        //无效订单
        $date['wu'] = db('order')->where(['assignor' => $this->us['user_id'], 'created_time' => ['between', [$start, $end]]])->where('state', 10)->count();
        //退单
        $date['tui'] = db('order')->join('contract', 'contract.orders_id = order.order_id', 'left')->where(['order.assignor' => $this->us['user_id']])->where('contract.con_time', '<', $start)//在签约时间以前的增项
        ->whereBetween('order.tui_time', [$start, $end])//在签约时间以前的增项
        ->where('order.state', 9)->count();

        //活动订单
        $date['huo'] = db('order')->where(['assignor' => $this->us['user_id'], 'created_time' => ['between', [$start, $end]]])->where('state', 'neq', 10)->where('channel_details', 96)->count();
        //历史活动订单
        $date['history'] = count($order);
        //成交的活动订单
        if (!empty($order_ids)) {
            $date['cheng_huo'] = db('order')->whereIn('order_id', $order_ids)->where('channel_details', 96)->count();
        } else {
            $date['cheng_huo'] = 0;
        }

        $shang_count = $date['jie'] - $date['huo'] - $date['wu'] + $date['cheng_huo'] + $date['history'];

        //上门量订单包括取消的
        if (!empty($shang_count)) {

            $date['turnovers'] = sprintf('%.2f', ((count($g1) - $date['tui']) / $shang_count) * 100) . '%';//上门成交率
        } else {
            if (empty($shang_count)) {
                $date['turnovers'] = 100;
            } else {
                $date['turnovers'] = 0;
            }

        }
        if ($type == 'ss') {
            return $date['turnovers'];
        }
        r_date($date, 200);
    }
}